Postgresql allows adding comments to objects such as tables. For example I've added a comment to table "mytable" by using this SQL command:
COMMENT ON TABLE mytable IS 'This is my table.';
My question is:
If I want to use a SQL-command to get all tables along with their respective comment - how would I do this? What would be the appropriate query for this?
Thanks in advance!
Cheers!
a_horse_with_no_name :
All comments are stored in pg_description\n\nTo get the comments on a table, you need to join it to pg_class\n\nAs an alternative you can also use the function obj_description() to retrieve this information:\n\nSELECT obj_description(oid)\nFROM pg_class\nWHERE relkind = 'r'\n\n\nEdit\n\nIn psql you can simply use the \\d+ command to show all tables including their comments. Or use the \\dd command to show all comments in the system",
2011-04-14T13:45:10
Ejrr1085 :
You can use pg_catalog.obj_description function and information_schema.tables schema view:\nSELECT t.table_name, pg_catalog.obj_description(pgc.oid, 'pg_class')\nFROM information_schema.tables t\nINNER JOIN pg_catalog.pg_class pgc\nON t.table_name = pgc.relname \nWHERE t.table_type='BASE TABLE'\nAND t.table_schema='public';\n\nFUNCTIONS-INFO-COMMENT-TABLE\nINFORMATION_SCHEMA Support in MySQL, PostgreSQL",
2020-11-07T20:43:51